package com.example.test1.serviceimpl;

import com.example.test1.bean.UserBean;
import com.example.test1.mapper.ExcelMapper;
import com.example.test1.service.ExcelService;
import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

@Service
public class ExcelServiceImpl implements ExcelService {
    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";
    private final static Logger logger =
            LoggerFactory.getLogger(ExcelServiceImpl.class);
    @Resource
    private ExcelMapper excelMapper;

    @Override
    public void exportExcelWithSimple(HttpServletRequest req,
                                      HttpServletResponse resp) {
        String fileName =  UUID.randomUUID().toString();
        fileName = fileName+".xls";
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet1 = workbook.createSheet("sheet1");
            sheet1.setDefaultRowHeightInPoints(20);//行高
            sheet1.setDefaultColumnWidth(20);//列宽

            HSSFRow headRow = sheet1.createRow(sheet1.getLastRowNum() + 1);
            headRow.createCell(0).setCellValue("姓名");
            headRow.createCell(headRow.getLastCellNum()).setCellValue("密码");
            headRow.createCell(headRow.getLastCellNum()).setCellValue("角色");
            int index = 1;
            HSSFRow bodyRow = null;
            List<UserBean> list = excelMapper.getAllUser();
            for(UserBean userBean:list) {
                bodyRow = sheet1.createRow(sheet1.getLastRowNum() + 1);
                index ++;
                bodyRow.createCell(0).setCellValue(userBean.getName());
                bodyRow.createCell(bodyRow.getLastCellNum())
                        .setCellValue(userBean.getPassword());
                bodyRow.createCell(bodyRow.getLastCellNum())
                        .setCellValue(userBean.getRole());
            }
            outExcelStream(resp,workbook,fileName);
        }catch (Exception ee){

        }
    }


    public void outExcelStream(HttpServletResponse response,
                               Workbook workbook,
                               String fileName){
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            response.setContentType("application/x-download");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-disposition",
                    "attachment;filename=" +
                            new String(fileName.getBytes(), "ISO8859-1")
                            + ".xls");
            workbook.write(os);
            os.flush();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(os!=null){
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public Integer importExcel(MultipartFile myFile) {
        List<UserBean> listUserBean = new ArrayList<UserBean>();
        Workbook workbook = null;
        String fileName = myFile.getOriginalFilename();//  获取文件名
        logger.info("【fileName】{}", fileName);
        if (fileName.endsWith(XLS)) {
            try {
                workbook = new HSSFWorkbook(myFile.getInputStream());//  2003版本
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else if (fileName.endsWith(XLSX)) {
            try {
                InputStream inputStream = myFile.getInputStream();
                workbook = new XSSFWorkbook(inputStream);//  2007版本
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        Sheet sheet = workbook.getSheet("sheet1");
        int rows = sheet.getLastRowNum();
        logger.info("【rows】{}", rows);
        if (rows == 0)
            return 0;
        long startTime = System.currentTimeMillis();
        for (int i = 1; i <= rows + 1; i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            UserBean userBean = new UserBean();
            String strName = getCellValue(row.getCell(0));
            userBean.setName(strName);
            String strPwd = getCellValue(row.getCell(1));
            userBean.setPassword(strPwd);
            String strRole = getCellValue(row.getCell(2));
            userBean.setRole(strRole);
            listUserBean.add(userBean);
        }
        excelMapper.batchInsert(listUserBean);
        long endTime = System.currentTimeMillis();
        long totaltime = endTime - startTime;
        logger.info("【消耗时间为】{}", totaltime);
        return rows;
    }

    private String getCellValue(Cell cell) {
        String value = "";
        if (cell == null)
            return value;
        if (cell.getCellType() == CellType.NUMERIC)
            value = cell.getNumericCellValue() + " ";
        else if (cell.getCellType() == CellType.STRING)
            value = cell.getStringCellValue();
        return value.trim();
    }
}
